﻿using Lima.ResumeSys.Class;
using Lima.ResumeSys.Models;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using static Lima.ResumeSys.Models.RewardPunish;

namespace Lima.ResumeSys.DAL
{
    public class RewardPunishDAL
    {
        private clsDbOperate dbOperate = new clsDbOperate();
        public RewardPunish ConvertToModel(DataRow row)
        {
            RewardPunish model = new RewardPunish();

            // 转换短文本字段
            if (!string.IsNullOrEmpty(row["RewId"].ToString()))
            {
                model.RewId = row["RewId"].ToString();
            }

            if (!string.IsNullOrEmpty(row["PerNo"].ToString()))
            {
                model.PerNo = row["PerNo"].ToString();
            }

            if (!string.IsNullOrEmpty(row["RewTitle"].ToString()))
            {
                model.RewTitle = row["RewTitle"].ToString();
            }

            // 转换长文本字段
            if (!string.IsNullOrEmpty(row["RewRecord"].ToString()))
            {
                model.RewRecord = row["RewRecord"].ToString();
            }

            // 转换日期/时间字段
            if (!string.IsNullOrEmpty(row["RewDate"].ToString()))
            {
                model.RewDate = Convert.ToDateTime(row["RewDate"]);
            }
            // 转换创建者和时间信息
            if (!string.IsNullOrEmpty(row["Creator"].ToString()))
            {
                model.Creator = row["Creator"].ToString();
            }

            if (!string.IsNullOrEmpty(row["CreateTime"].ToString()))
            {
                model.CreateTime = Convert.ToDateTime(row["CreateTime"]);
            }

            if (!string.IsNullOrEmpty(row["UpdatedBy"].ToString()))
            {
                model.UpdatedBy = row["UpdatedBy"].ToString();
            }

            if (!string.IsNullOrEmpty(row["UpdateTime"].ToString()))
            {
                model.UpdateTime = Convert.ToDateTime(row["UpdateTime"]);
            }

            return model;
        }
        public RewardPunish GetModel(string rewId)
        {
            try
            {
                string sql = @"select * from RewardPunish where RewId=@rewId ";
                OleDbParameter[] parameters = { new OleDbParameter("@rewId", rewId) };
                DataTable dt = dbOperate.ExecuteQuery(sql, parameters);
                if (dt != null && dt.Rows.Count > 0)
                {
                    DataRow row = dt.Rows[0];
                    return ConvertToModel(row);
                }
                else
                {
                    return null;
                }
            }
            catch (Exception ex)
            {
                throw;
            }

        }

        public bool Del(string rewId) {
            try
            {
                string sql = @"delete from RewardPunish where RewId=@rewId ";
                OleDbParameter[] parameters = { new OleDbParameter("@rewId", rewId) };
                int result = dbOperate.ExecuteNonQuery(sql, parameters);
                return result > 0;
            }
            catch (Exception ex)
            {
                return false;
            }
        }

        public int GetResultCount(string rewTitle)
        {
            string sql = @"select * from RewardPunish where RewTitle='@rewTitle'";
            OleDbParameter[] parameters = {
            new OleDbParameter("@rewTitle", rewTitle) };
            DataTable dt = dbOperate.ExecuteQuery(sql, parameters);

            return dt.Rows.Count; ;
        }

        public bool Add(RewardPunish model)
        {
            try
            {
                string sql = @"INSERT INTO RewardPunish(
                        RewId, 
                        PerNo, 
                        RewTitle, 
                        RewRecord, 
                        RewDate, 
                        Creator, 
                        CreateTime, 
                        UpdatedBy, 
                        UpdateTime
                    ) VALUES (
                        @RewId, 
                        @PerNo, 
                        @RewTitle, 
                        @RewRecord, 
                        @RewDate, 
                        @Creator, 
                        @CreateTime, 
                        @UpdatedBy, 
                        @UpdateTime
                    )";

                OleDbParameter[] parameters = {
            new OleDbParameter("@RewId", model.RewId),
            new OleDbParameter("@PerNo", model.PerNo),
            new OleDbParameter("@RewTitle", model.RewTitle),
            new OleDbParameter("@RewRecord", model.RewRecord),
            new OleDbParameter("@RewDate", model.RewDate),
            new OleDbParameter("@Creator", model.Creator),
            new OleDbParameter("@CreateTime", DateTime.Now.ToString()),
            new OleDbParameter("@UpdatedBy", model.UpdatedBy),
            new OleDbParameter("@UpdateTime", DateTime.Now.ToString())
        };

                int result = dbOperate.ExecuteNonQuery(sql, parameters);
                return result > 0;
            }
            catch
            {
                return false;
            }
        }
        public bool Update(RewardPunish model)
        {
            try
            {
                string sql = @"UPDATE RewardPunish SET 
                        RewTitle = @RewTitle, 
                        RewRecord = @RewRecord, 
                        RewDate = @RewDate, 
                        UpdatedBy = @UpdatedBy, 
                        UpdateTime = @UpdateTime
                    WHERE RewId = @RewId";

                OleDbParameter[] parameters = {
            new OleDbParameter("@RewTitle", model.RewTitle),
            new OleDbParameter("@RewRecord", model.RewRecord),
            new OleDbParameter("@RewDate", model.RewDate),
            new OleDbParameter("@UpdatedBy", model.UpdatedBy),
            new OleDbParameter("@UpdateTime", DateTime.Now.ToString()),
            new OleDbParameter("@RewId", model.RewId)
        };

                int result = dbOperate.ExecuteNonQuery(sql, parameters);
                return result > 0;
            }
            catch
            {
                return false;
            }
        }
        public List<RewardPunish> GetList(QueryRewardPunishModel query)
        {
            string strWhere = "";
            List<OleDbParameter> parameters = new List<OleDbParameter>();

            if (!string.IsNullOrWhiteSpace(query.PerNo))
            {
                strWhere += " and PerNo = @PerNo ";
                parameters.Add(new OleDbParameter("@PerNo", query.PerNo));
            }

            string sql = "SELECT * FROM RewardPunish WHERE 1=1 " + strWhere +
                        " ORDER BY RewDate DESC ";
            List<RewardPunish> list = new List<RewardPunish>();
            //获取数据列表
            var dataTables = dbOperate.ExecuteQuery(sql, parameters.ToArray());

            if (dataTables != null && dataTables.Rows.Count > 0)
            {
                foreach (DataRow row in dataTables.Rows)
                {
                    list.Add(ConvertToModel(row));
                }
            }
            return list;
        }
    }
}
